package com.example.database.mysql.userdb;

import com.example.database.utils.DBOpenUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * user表的增删改查
 * */
public class UserDBService {
    private Connection conn=null; //打开数据库对象
    private PreparedStatement ps=null;//操作整合sql语句的对象
    private ResultSet rs=null;

    //UserDBService 对象
    public static UserDBService dbService=null;

    /**
     * 构造方法 私有化
     * */
    public UserDBService(){
    }

    /**
     * 获取MySQL数据库单例类对象
     * */
    public static UserDBService getDbService(){
        if(dbService==null){
            dbService=new UserDBService();
        }
        return dbService;
    }

    // 登录 根据手机号码查询
    public User findUserByPhoneNum(String phoneNumber) {
        User user = new User();
        //MySQL 语句
        String sql = "select * from user where phoneNumber = ?";
        //获取链接数据库对象
        conn = DBOpenUtil.getConn();
        try {
            if (conn != null && (!conn.isClosed())) {
                ps = (PreparedStatement) conn.prepareStatement(sql);
                ps.setString(1,phoneNumber);
                if (ps != null) {
                    rs = ps.executeQuery();
                    if (rs.next()) {
                        user.setPhoneNumber(rs.getString("phoneNumber"));
                        user.setPassWord(rs.getString("passWord"));
                        user.setNickName(rs.getString("nickName"));
                        user.setPhoto(rs.getString("photo"));
                        user.setSex(rs.getString("sex"));
                        user.setDateOfBirth(rs.getString("dateOfBirth"));
                        user.setPersonalSignature(rs.getString("personalSignature"));
                        user.setIsVolunteers(rs.getInt("isVolunteers"));
                        user.setRegistrationTime(rs.getString("registrationTime"));
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        DBOpenUtil.closeAll(conn,ps,rs);//关闭相关操作
        return user;
    }

    // 注册 插入一条数据
    public int addUser(User user) {
        int result = -1;
        if (user != null) {
            //获取链接数据库对象
            conn= DBOpenUtil.getConn();
            String sql = "insert into user (phoneNumber,passWord,nickName,photo,sex,dateOfBirth,personalSignature,isVolunteers,registrationTime) values (?,?,?,?,?,?,?,?,?)";
            try {
                boolean closed = conn.isClosed();
                if (conn != null && !closed) {
                    ps = (PreparedStatement) conn.prepareStatement(sql);
                    String phoneNumber = user.getPhoneNumber();
                    String passWord = user.getPassWord();
                    String nickName = user.getNickName();
                    String photo = user.getPhoto();
                    String sex = user.getSex();
                    String dateOfBirth = user.getDateOfBirth();
                    String personalSignature = user.getPersonalSignature();
                    int isVolunteers = user.getIsVolunteers();
                    String registrationTime = user.getRegistrationTime();
                    ps.setString(0,phoneNumber);
                    ps.setString(1,passWord);
                    ps.setString(2,nickName);
                    ps.setString(3,photo);
                    ps.setString(4,sex);
                    ps.setString(5,dateOfBirth);
                    ps.setString(6,personalSignature);
                    ps.setInt(7,isVolunteers);
                    ps.setString(8,registrationTime);
                    result = ps.executeUpdate(); //返回1, 执行成功
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        DBOpenUtil.closeAll(conn,ps);//关闭相关操作
        return result;
    }

}
